#!C:/Perl/bin/perl.exe
use CGI;
use DBI;
use strict;
use warnings;
use feature "switch";

open DEBUG, ">", "debug.txt" or die "Can't open debug log: $!";

# Strand names and letters, indexed in the same order as the database tables.
my @strands = (
    "Number Sense and Operations",
    "Patterns, Relations, and Algebra",
    "Geometry",
    "Measurement",
    "Data Analysis, Statistics, and Probability"
);
my @strandLetters = ('N', 'P', 'G', 'M', 'D');
my %strandNumber = ('N'=>0, 'P'=>1, 'G'=>2, 'M'=>3, 'D'=>4);

my $styleBlock=<<EOT;
body { font-family: sans-serif; font-size: medium }
select { font-size: small }
button { font-size: medium; margin: 10px }
td { vertical-align: top }
.label_tape { font-size: small; background-color: #400; color: white; padding-left: 2px; padding-right: 2px }
.control_panel { background-color: wheat; border-color: #400; border-style: solid; border-width: thin }
.instructions { font-size: small; color: #400; padding: 10px }
.standard_text_cell { font-size: small; }
EOT

my $javascript=<<EOT;
function resetSelections() {
    document.getElementsByName('teacher_selection')[0].selectedIndex = 0;
    document.getElementsByName('grade_selection')[0].selectedIndex = 0;
    document.getElementsByName('course_selection')[0].selectedIndex = 0;
    document.getElementsByName('student_selection')[0].selectedIndex = 0;
    document.getElementsByName('strand_selection')[0].selectedIndex = 0;
    document.getElementsByName('standard_selection')[0].selectedIndex = 0;
    document.getElementById('selection_form').submit();
}
EOT


# Get the HTTP request, $q, record parameters, and start a response.
my $q = CGI->new;
my $teacher = $q->param('teacher_selection');
my $grade = $q->param('grade_selection');
my $course = $q->param('course_selection');
my $student = $q->param('student_selection');
my $strand = $q->param('strand_selection');
my $standard = $q->param('standard_selection');

# Create SQL query statements.
sub where {
    # Return an SQL WHERE clause for a given label, or an empty string
    # if no filtering is indicated.
    my $label = shift;
    my @where;
    given($label) {
        when ('teacher') {
            push @where, 'report.grade = ' . $grade if $grade;
            push @where, 'report.course = ' . $course if $course;
            push @where, 'report.student = ' . $student if $student;
        }
        when ('grade') {
            push @where, 'report.teacher = ' . $teacher if $teacher;
            push @where, 'report.course = ' . $course if $course;
            push @where, 'report.student = ' . $student if $student;
        }
        when ('course') {
            push @where, 'report.grade = ' . $grade if $grade;
            push @where, 'report.teacher = ' . $teacher if $teacher;
            push @where, 'report.student = ' . $student if $student;
        }
        when ('student') {
            push @where, 'report.grade = ' . $grade if $grade;
            push @where, 'report.teacher = ' . $teacher if $teacher;
            push @where, 'report.course = ' . $course if $course;
        }
        when ('strand') {
            push @where, 'standard.rowid = ' . $standard if $standard;
        }
        when ('standard') {
            push @where, 'strand.rowid = ' . $strand if $strand;
            push @where, 'standard.grade = ' . $grade if $grade;
        }
    }
    if (@where) {
        return ' where ' . join(' and ', @where);
    } else {
        return '';
    }
}

my %select = (
    teacher => 'select distinct teacher.rowid, teacher.name from teacher join report on report.teacher = teacher.rowid'
        . where('teacher') . ' order by teacher.name;',
    grade => 'select distinct grade, grade from report'
        . where('grade') . ' order by grade;',
    course => 'select distinct course.rowid, course.name from course join report on report.course = course.rowid'
        . where('course') . ' order by course.name;',
    student => 'select distinct student.rowid, student.name from student join report on report.student = student.rowid'
        . where('student') . ' order by student.lastName, student.name;',
    strand => 'select distinct strand.rowid, symbol || ": " || strand.text from strand join standard on standard.strand = strand.number'
        . where('strand') . ' order by strand.number;',
    standard => 'select distinct standard.rowid, standard.grade || "." || strand.symbol || "." || standard.number || ": " || substr(standard.text, 1, 110) || case when length(standard.text) > 110 then "..." else "" end from standard join strand on standard.strand = strand.number'
        . where('standard') . ' order by standard.grade, standard.strand, standard.number;',
);

# Create HTML content.
print $q->header, $q->start_html(-title=>'ALEKS data', -style=>{'-code'=>$styleBlock}, -script=>$javascript );

# Open the repository (SQLite database).
my $dbfile = 'aleks.sqlite';  # in the current directory
my $db = DBI->connect("dbi:SQLite:dbname=$dbfile","","") or die $DBI::errstr;
my $stmt;  # a temporary prepared SQL statement holder
my @row;  # a temporary row holder

sub menu {
    # Create a popup menu (SELECT element).
    my $label = shift;  # menu label and name, with _selection
    my $selectSQL = shift;  # rowid and text label
    my @value;
    my %label;
    my @row;
    my $stmt = $db->prepare($selectSQL) or die $db->errstr;
    # First entry, index 0, represents all values, i.e., no filtering.
    push @value, 0;
    $label{0} = '(all)';
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @value, $row[0];
        $label{$row[0]} = $row[1];
    }
    return $q->label(
        $q->span({-class=>'label_tape'}, $label), 
        $q->br,
        $q->popup_menu(
            -name=>$label . '_selection', 
            -values=>\@value, 
            -labels=>\%label,
            -default=>[0],
            -onChange=>'this.form.submit()'
        )
    );
}

sub filteredMenu {
    # Create a filtered popup menu (SELECT element) for a given label.
    my $label = shift;  # menu label, menu name (. _selection), and key to SQL query text
    my $sql = $select{$label};
    my $stmt = $db->prepare($sql) or die $db->errstr;
    my @value;  # option values
    my %label;  # option text
    my @row;
    # The first entry always represents 'all' values, i.e. no filtering.
    push @value, 0;  # used in boolean contexts
    $label{0} = '(all)';
    # Subsequent values are usually rowid's from the database.
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @value, $row[0];  # rowid or grade
        $label{$row[0]} = $row[1];  # text
    }
    # Wrap a SELECT element in a styled label.
    return $q->label(
        $q->span({-class=>'label_tape'}, $label), 
        $q->br,  # keeps label on top of menu
        $q->popup_menu(
            -name=>$label . '_selection', 
            -values=>\@value, 
            -labels=>\%label,
            -default=>[0],
            -onChange=>'this.form.submit()'
        )
    );
}

sub listItems {
    my $standard = shift;
    my ($grade, $symbol, $number, @row, @item);
    ($grade, $symbol, $number) = split /\./, $standard, 3;
    $number =~ s/(\d*).*/$1/;
    my $sql = 'select rowid from standard where grade = ? and strand = ? and number = ?;';
    my $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($grade, $strandNumber{$symbol}, $number) or die $stmt->errstr;
    if (@row = $stmt->fetchrow_array) {
        $standard = $row[0];
    }
    $stmt->finish;
    $sql = 'select text from item where standard = ?';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute($standard) or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @item, $row[0];
    }
    return $q->ul(\@item);
}

sub standardText {
    # Return the full text for a given standard rowid.
    my $rowid = shift;
    my $stmt = $db->prepare('select text from standard where rowid = ' . $rowid) or die $db->errstr;
    $stmt->execute() or die $stmt->errstr;
    if (my @row = $stmt->fetchrow_array) {
        return $row[0];
    }
    return '';
}

# Create a form for data selection.
sub dataSelection {
    my $t;  # text
    $t = $q->start_form(-method=>'GET', -id=>'selection_form');
            
    my $studentSide = 
        $q->table(
            $q->Tr(
                [$q->td(filteredMenu('teacher')), 
                 $q->td(filteredMenu('grade')), 
                 $q->td(filteredMenu('course')), 
                 $q->td(filteredMenu('student')),
                ]
            )
        );
    my $assessmentSide = 
        $q->table(
            $q->Tr(
                [$q->td(filteredMenu('strand')), 
                 $q->td(filteredMenu('standard')),
                 $q->td({-class=>'standard_text_cell'}, standardText $standard),
                ]
            )
        );
    $t .= $q->table({-class=>'control_panel'}, 
        $q->Tr(
            [
            $q->td({-class=>'instructions'}, <<EOInstructions
                Selection menus filter each other.<br>
                To show all entries in all menus, press the reset button .
EOInstructions
            ),
            $q->td(
                $q->table(
                    $q->Tr(
                        [
                        $q->td([$studentSide, $assessmentSide]),
                        ]
                    )
                )
            ),
            $q->td('<button type="button" onclick="resetSelections()">reset</button>')
            ]
        )
    );

    $t .= $q->end_form;
    return $t;
}

print dataSelection;
if ($student) {
    print studentReport;
}
print $q->p([$teacher, $grade, $course, $student, $strand, $standard]);
print $q->p([$select{'teacher'}, $select{'grade'}, $select{'course'}, $select{'student'}, $select{'strand'}, $select{'standard'}]);

#if ($standard ne '(all)') {
#    print listItems($standard);
#}

print $q->end_html;

# Disconnect from the database
$db->disconnect;

sub studentReport {
    my @record;
    my @grade;
    my @teacher;
    my $sql = 'select record, grade, teacher from report where student = ' . $student . ';';
    $stmt = $db->prepare($sql) or die $db->errstr;
    $stmt->execute() or die $stmt->errstr;
    while (@row = $stmt->fetchrow_array) {
        push @record, $row[0];
        push @grade, $row[1];
        push @teacher, $row[2];
    }
    return $q->ul();
}

sub percent {
    # Avoid dividing by zero.
    my $numerator = shift;
    my $denominator = shift;
    if ($denominator == 0) {
        return 0;
    } else {
        return $numerator / $denominator * 100;
    }
}

# Create pie charts.
sub percentPie {
    # E.g., print percentPie(50, 70); would should 50% items mastered in a 70x70 pixel graphic.
    my $percent = shift;
    my $size = shift;
    if (!defined $size) { $size = 70 }
    $size = sprintf('%.0f', $size);
    my $data = sprintf('%.0f', $percent) . ',' . sprintf('%.0f', 100 - $percent);
    my $chartPiece1 = '<img class="pieImg" src="http://chart.apis.google.com/chart?chs=SZxSZ&cht=p&chco=008000&chd=t:';
    my $chartPiece2 = '&chp=4.71" width="SZ" height="SZ" alt="percentage of items mastered" />';
    my $html = $chartPiece1 . $data . $chartPiece2;
    $html =~ s/SZ/$size/g;
    return $html;
}

close DEBUG;
